﻿using System.Text;
using System;
namespace Grishko.DataTier.SqlScriptor
{
    /// <summary>
    /// вспомогательный класс движка SqlScript.    
    /// Сформировать скрипт - позволяющий сформировать дерево свойств
    /// пройтись по графу указаного типа и выявить все используемые TagName в запросе.
    /// Сосотавить скрипт соответ - BuildSearchArea_1
    /// </summary>
    public class ScriprPropertyTreeEngine
    {        
        private SelectCommand selectCommand = null;
        public ScriprPropertyTreeEngine(SelectCommand cmd)
        {
            selectCommand = cmd;
        }

        public string GetScript()
        {            
            return GetObjectTagRecords();

        }

        public string GetObjectTagRecords()
        {
            string[] ScriptParams = { SqlScriptEngine .ScriptConst_SrchRoot, 
                                      SqlScriptEngine .ScriptConst_SerachAreaTable, 
                                    "два", 
                                    "", 
                                    "", 
                                    "" };


            string FormatString =
                                " ;WITH " + Environment.NewLine +
                                " TREE (ObjectID, PropertyName,TxtPropertyName, PropertyValue, TxtPropertyValue ,IsObject,LinkProp,ValueIsSqlFunction ) " + Environment.NewLine +
                                " AS ( " + Environment.NewLine +
                                "-- Корни дерева - записи свойства отвечающие уловиям отбора " + Environment.NewLine +
                                " SELECT " + Environment.NewLine +
                                " srch.ObjectID, srch.PropertyName, srch.TxtPropertyName, srch.PropertyValue,srch.TxtPropertyValue, srch.isobject, srch.PropertyValue as LinkProp, srch.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM " + Environment.NewLine +
                                "		{1}  srch       				" + Environment.NewLine +
                                "		JOIN {0} SrchRoot on SrchRoot.ObjectID =srch.ObjectID " + Environment.NewLine +
                                " WHERE " + Environment.NewLine +
                                "	srch.ISObject=1	{3}" + Environment.NewLine +
                                " UNION ALL" + Environment.NewLine +
                                "-- Подзапрос -  вниз" + Environment.NewLine +
                                " SELECT " + Environment.NewLine +
                                " T.ObjectID, " + Environment.NewLine +
                                " CAST((T.PropertyName +'.'+ V.PropertyName)   as varchar(255)) as PropertyName," + Environment.NewLine +
                                " CAST((T.TxtPropertyName +'.'+ V.TxtPropertyName)   as varchar(255)) as TxtPropertyName," + Environment.NewLine +
                                " V.PropertyValue, V.TxtPropertyValue, t.isobject, V.PropertyValue as LinkProp,  V.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM {1} V " + Environment.NewLine +
                                " JOIN TREE T " + Environment.NewLine +
                                " ON " + Environment.NewLine +
                                " T.PropertyValue= V.objectid " + Environment.NewLine +
                                " and V.isobject=1 )     " + Environment.NewLine +
                                " -- Получаем дерево объектов - и цепляем описательные свойства" + Environment.NewLine +
                                " INSERT INTO #PopulatedObjectTree  SELECT " + Environment.NewLine +
                                " tree.ObjectId,  " + Environment.NewLine +
                                " CAST((tree.PropertyName +'.'+ ObjectPropVal.Propertyname )   as varchar(255)) as PropertyName , " + Environment.NewLine +
                                " CAST((tree.TxtPropertyName+'.'+ ObjectPropVal.TxtPropertyName  )   as varchar(255)) as TxtProperty," + Environment.NewLine +
                                " ObjectPropVal.PropertyValue, " + Environment.NewLine +
                                " ObjectPropVal.TxtPropertyValue, " + Environment.NewLine +
                                " ObjectPropVal.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM tree  " + Environment.NewLine +
                                " join {1} ObjectPropVal on ObjectPropVal.objectid=tree.LinkProp" + Environment.NewLine +
                                " WHERE " + Environment.NewLine +
                                " ObjectPropVal.isobject=0" + Environment.NewLine +
                                "{4}" + Environment.NewLine +
                                " UNION " + Environment.NewLine +
                                " SELECT " + Environment.NewLine +
                                "   SAT.ObjectId,  " + Environment.NewLine +
                                "   SAT.PropertyName ," + Environment.NewLine +
                                "   SAT.TxtPropertyName,		" + Environment.NewLine +
                                "   SAT.PropertyValue, " + Environment.NewLine +
                                "   SAT.TxtPropertyValue, " + Environment.NewLine +
                                "   SAT.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM " + Environment.NewLine +
                                " {1} SAT JOIN {0} on SAT.ObjectId={0}.ObjectId " + Environment.NewLine +
                                " WHERE " + Environment.NewLine +
                                "   SAT.isobject=0" + Environment.NewLine +
                                "{5}";

            return string.Format(FormatString, ScriptParams);

          
        }

        
    }
}